﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class AttendConfigDao
    {
        public bool Delete(SqlConnection conn, int dutyTypeId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF NOT EXISTS(SELECT [DutyType] FROM [dbo].[Users] WHERE [DutyType] = @DutyTypeId) ");
            builder.Append("BEGIN ");
            builder.Append("\tDELETE FROM [dbo].[Attend_Config] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[DutyTypeId] = @DutyTypeId ");
            builder.Append("END ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@DutyTypeId", SqlDbType.Int) };
            commandParameters[0].Value = dutyTypeId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public DataTable GetUserConfig(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Attend_Config].* ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Attend_Config], ");
            builder.Append("\t[dbo].[Users] ");
            builder.Append("WHERE ");
            builder.Append("\t[Attend_Config].[DutyTypeId] = [Users].[DutyType] ");
            builder.Append("\tAND [Users].[UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Attend_Config";
            return set.Tables[0];
        }

        public int Insert(SqlConnection conn, AttendConfigData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Attend_Config] ");
            builder.Append("( ");
            builder.Append("\t[DutyName], ");
            builder.Append("\t[General], ");
            builder.Append("\t[DutyTime1], ");
            builder.Append("\t[DutyTime2], ");
            builder.Append("\t[DutyTime3], ");
            builder.Append("\t[DutyTime4], ");
            builder.Append("\t[DutyTime5], ");
            builder.Append("\t[DutyTime6], ");
            builder.Append("\t[DutyType1], ");
            builder.Append("\t[DutyType2], ");
            builder.Append("\t[DutyType3], ");
            builder.Append("\t[DutyType4], ");
            builder.Append("\t[DutyType5], ");
            builder.Append("\t[DutyType6] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@DutyName, ");
            builder.Append("\t@General, ");
            builder.Append("\t@DutyTime1, ");
            builder.Append("\t@DutyTime2, ");
            builder.Append("\t@DutyTime3, ");
            builder.Append("\t@DutyTime4, ");
            builder.Append("\t@DutyTime5, ");
            builder.Append("\t@DutyTime6, ");
            builder.Append("\t@DutyType1, ");
            builder.Append("\t@DutyType2, ");
            builder.Append("\t@DutyType3, ");
            builder.Append("\t@DutyType4, ");
            builder.Append("\t@DutyType5, ");
            builder.Append("\t@DutyType6 ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[14];
            commandParameters[0] = new SqlParameter("@DutyName", SqlDbType.NVarChar, 100);
            commandParameters[0].Value = data.DutyName;
            commandParameters[1] = new SqlParameter("@General", SqlDbType.NVarChar, 50);
            if (data.GeneralIsNull)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = data.General;
            }
            commandParameters[2] = new SqlParameter("@DutyTime1", SqlDbType.VarChar);
            if (data.DutyTime1IsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.DutyTime1;
            }
            commandParameters[3] = new SqlParameter("@DutyTime2", SqlDbType.VarChar);
            if (data.DutyTime2IsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.DutyTime2;
            }
            commandParameters[4] = new SqlParameter("@DutyTime3", SqlDbType.VarChar);
            if (data.DutyTime3IsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.DutyTime3;
            }
            commandParameters[5] = new SqlParameter("@DutyTime4", SqlDbType.VarChar);
            if (data.DutyTime4IsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.DutyTime4;
            }
            commandParameters[6] = new SqlParameter("@DutyTime5", SqlDbType.VarChar);
            if (data.DutyTime5IsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.DutyTime5;
            }
            commandParameters[7] = new SqlParameter("@DutyTime6", SqlDbType.VarChar);
            if (data.DutyTime6IsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.DutyTime6;
            }
            commandParameters[8] = new SqlParameter("@DutyType1", SqlDbType.Int);
            commandParameters[8].Value = data.DutyType1;
            commandParameters[9] = new SqlParameter("@DutyType2", SqlDbType.Int);
            commandParameters[9].Value = data.DutyType2;
            commandParameters[10] = new SqlParameter("@DutyType3", SqlDbType.Int);
            commandParameters[10].Value = data.DutyType3;
            commandParameters[11] = new SqlParameter("@DutyType4", SqlDbType.Int);
            commandParameters[11].Value = data.DutyType4;
            commandParameters[12] = new SqlParameter("@DutyType5", SqlDbType.Int);
            commandParameters[12].Value = data.DutyType5;
            commandParameters[13] = new SqlParameter("@DutyType6", SqlDbType.Int);
            commandParameters[13].Value = data.DutyType6;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public bool IsUsed(SqlConnection conn, int dutyTypeId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF EXISTS(SELECT [DutyType] FROM [dbo].[Users] WHERE [DutyType] = @DutyTypeId) ");
            builder.Append("\tSELECT 1 ");
            builder.Append("ELSE ");
            builder.Append("\tSELECT 0 ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@DutyTypeId", SqlDbType.Int) };
            commandParameters[0].Value = dutyTypeId;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            return ((obj2 != null) && Convert.ToBoolean(obj2));
        }

        public DataTable SelectAll(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[DutyTypeId], ");
            builder.Append("\t[DutyName], ");
            builder.Append("\t[General], ");
            builder.Append("\t[DutyTime1], ");
            builder.Append("\t[DutyTime2], ");
            builder.Append("\t[DutyTime3], ");
            builder.Append("\t[DutyTime4], ");
            builder.Append("\t[DutyTime5], ");
            builder.Append("\t[DutyTime6], ");
            builder.Append("\t[DutyType1], ");
            builder.Append("\t[DutyType2], ");
            builder.Append("\t[DutyType3], ");
            builder.Append("\t[DutyType4], ");
            builder.Append("\t[DutyType5], ");
            builder.Append("\t[DutyType6] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Attend_Config] ");
            builder.Append("ORDER BY ");
            builder.Append("\t[DutyTypeId] ASC ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Attend_Config";
            return set.Tables[0];
        }

        public DataTable SelectByDutyTypeId(SqlConnection conn, int dutyTypeId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[DutyTypeId], ");
            builder.Append("\t[DutyName], ");
            builder.Append("\t[General], ");
            builder.Append("\t[DutyTime1], ");
            builder.Append("\t[DutyTime2], ");
            builder.Append("\t[DutyTime3], ");
            builder.Append("\t[DutyTime4], ");
            builder.Append("\t[DutyTime5], ");
            builder.Append("\t[DutyTime6], ");
            builder.Append("\t[DutyType1], ");
            builder.Append("\t[DutyType2], ");
            builder.Append("\t[DutyType3], ");
            builder.Append("\t[DutyType4], ");
            builder.Append("\t[DutyType5], ");
            builder.Append("\t[DutyType6] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Attend_Config] ");
            builder.Append("WHERE ");
            builder.Append("\t[DutyTypeId] = @DutyTypeId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@DutyTypeId", SqlDbType.Int) };
            commandParameters[0].Value = dutyTypeId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Attend_Config";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, AttendConfigData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Attend_Config] SET ");
            builder.Append("\t[DutyName] = @DutyName, ");
            builder.Append("\t[General] = @General, ");
            builder.Append("\t[DutyTime1] = @DutyTime1, ");
            builder.Append("\t[DutyTime2] = @DutyTime2, ");
            builder.Append("\t[DutyTime3] = @DutyTime3, ");
            builder.Append("\t[DutyTime4] = @DutyTime4, ");
            builder.Append("\t[DutyTime5] = @DutyTime5, ");
            builder.Append("\t[DutyTime6] = @DutyTime6, ");
            builder.Append("\t[DutyType1] = @DutyType1, ");
            builder.Append("\t[DutyType2] = @DutyType2, ");
            builder.Append("\t[DutyType3] = @DutyType3, ");
            builder.Append("\t[DutyType4] = @DutyType4, ");
            builder.Append("\t[DutyType5] = @DutyType5, ");
            builder.Append("\t[DutyType6] = @DutyType6 ");
            builder.Append("WHERE ");
            builder.Append("\t[DutyTypeId] = @DutyTypeId ");
            SqlParameter[] commandParameters = new SqlParameter[15];
            commandParameters[0] = new SqlParameter("@DutyTypeId", SqlDbType.Int);
            commandParameters[0].Value = data.DutyTypeId;
            commandParameters[1] = new SqlParameter("@DutyName", SqlDbType.NVarChar, 100);
            commandParameters[1].Value = data.DutyName;
            commandParameters[2] = new SqlParameter("@General", SqlDbType.NVarChar, 50);
            if (data.GeneralIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.General;
            }
            commandParameters[3] = new SqlParameter("@DutyTime1", SqlDbType.VarChar);
            if (data.DutyTime1IsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.DutyTime1;
            }
            commandParameters[4] = new SqlParameter("@DutyTime2", SqlDbType.VarChar);
            if (data.DutyTime2IsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.DutyTime2;
            }
            commandParameters[5] = new SqlParameter("@DutyTime3", SqlDbType.VarChar);
            if (data.DutyTime3IsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.DutyTime3;
            }
            commandParameters[6] = new SqlParameter("@DutyTime4", SqlDbType.VarChar);
            if (data.DutyTime4IsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.DutyTime4;
            }
            commandParameters[7] = new SqlParameter("@DutyTime5", SqlDbType.VarChar);
            if (data.DutyTime5IsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.DutyTime5;
            }
            commandParameters[8] = new SqlParameter("@DutyTime6", SqlDbType.VarChar);
            if (data.DutyTime6IsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.DutyTime6;
            }
            commandParameters[9] = new SqlParameter("@DutyType1", SqlDbType.Int);
            commandParameters[9].Value = data.DutyType1;
            commandParameters[10] = new SqlParameter("@DutyType2", SqlDbType.Int);
            commandParameters[10].Value = data.DutyType2;
            commandParameters[11] = new SqlParameter("@DutyType3", SqlDbType.Int);
            commandParameters[11].Value = data.DutyType3;
            commandParameters[12] = new SqlParameter("@DutyType4", SqlDbType.Int);
            commandParameters[12].Value = data.DutyType4;
            commandParameters[13] = new SqlParameter("@DutyType5", SqlDbType.Int);
            commandParameters[13].Value = data.DutyType5;
            commandParameters[14] = new SqlParameter("@DutyType6", SqlDbType.Int);
            commandParameters[14].Value = data.DutyType6;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

